#!/usr/local/php/bin/php
# 说明：对比静态配置表，生成差异SQL

<?php

set_time_limit(0);
ini_set('memory_limit', '1024M');

// 来源DB
define('SRC_DB_HOST', 'dev.69night.cn:3306');
define('SRC_DB_NAME', 'sn_core');
define('SRC_DB_USER', 'root');
define('SRC_DB_PASS', 'Morecruit_1234');

// 目标DB
define('DEST_DB_HOST', 'dev.69night.cn:3306');
define('DEST_DB_NAME', '__bak_sn_core');
define('DEST_DB_USER', 'root');
define('DEST_DB_PASS', 'Morecruit_1234');

// 比较并生成两个DB的差异SQL
class Controller_CreateDiffSql
{
    // 需要手动同步的表，不需要自动同步
    private $_exceptTables = array(
        // 'name_dict',
    );

    // 不需要同步的字段名
    private $_exceptFields = array(
        'status', 'display_order'
    );

    // 各表中需要排除的字段
    private $_tableExceptFields = array(
        // 'port_level' => array('title_1', 'title_2', 'title_3'),
    );

    /*
        执行命令：
            ./create_diff_sql.php voyage_static_zh_cn bak__voyage_static_zh_cn [ship] [captain] ....
        注意：可以不传表名，即缺省为源数据库所有表
    */
    public function execute()
    {
        $startTime = microtime(true);

        // 源数据库
        $sourceDb = isset($_SERVER['argv'][1]) ? $_SERVER['argv'][1] : SRC_DB_NAME;

        // 目的数据库
        $destDb = isset($_SERVER['argv'][2]) ? $_SERVER['argv'][2] : DEST_DB_NAME;

        if (! $sourceDb || ! $destDb) {
            exit('plz input source/dest database');
        }

        // 要比较哪些表
        $sourceTables = isset($_SERVER['argv'][3]) ? array_slice($_SERVER['argv'], 3) : array();

        // 来源DB
        $conn1 = mysql_connect(SRC_DB_HOST, SRC_DB_USER, SRC_DB_PASS) or die("Could not connect: " . mysql_error());
        mysql_query('SET NAMES UTF8', $conn1);

        // 目标DB
        $conn2 = mysql_connect(DEST_DB_HOST, DEST_DB_USER, DEST_DB_PASS) or die("Could not connect: " . mysql_error());
        mysql_query('SET NAMES UTF8', $conn2);

        // 检测来源库是否存在
        if (! $this->_isDbExist($conn1, $sourceDb)) {
            exit('source db doesnt exist');
        }

        // 检测目标库是否存在
        if (! $this->_isDbExist($conn2, $destDb)) {
            exit('dest db doesnt exist');
        }

        // 缺省比较来源库的所有表
        if (! $sourceTables) {
            $sourceTables = $this->_getTables($conn1, $sourceDb);
        }

        // 排除掉需不需要同步的表
        $sourceTables = array_diff($sourceTables, $this->_exceptTables);

        $insertSql = $deleteSql = $updateSql = '';

        foreach ($sourceTables as $table) {

            // 检测目标库中是否有这张表
            if (! $this->_isTableExist($conn2, $destDb, $table)) {
                exit('table ' . $table . ' is not in destDb');
            }

            // 排除不需要同步的字段
            if (isset($this->_tableExceptFields[$table])) {
                $excepts = array_merge($this->_exceptFields, $this->_tableExceptFields[$table]);
            } else {
                $excepts = $this->_exceptFields;
            }

            // 读取源数据库中指定的表所有主键
            $sourcePks = $this->_fetchPks($conn1, $sourceDb, $table);

            // 读取目的数据库中指定的表所有主键
            $destPks   = $this->_fetchPks($conn2, $destDb, $table);

            // 需要insert的记录
            if ($insertPks = array_diff($sourcePks, $destPks)) {
                $insertSql .= $this->_createInsert($conn1, $sourceDb, $table, $insertPks);
            }

            // 需要delete的记录
            if ($deletePks = array_diff($destPks, $sourcePks)) {
                $deleteSql .= $this->_createDelete($table, $deletePks);
            }

            // 需要update的记录
            if ($updatePks = array_intersect($destPks, $sourcePks)) {
                $updateSql .= $this->_createUpdate($conn1, $conn2, $sourceDb, $destDb, $table, $excepts, $updatePks);
            }
        }

        $allSql = $insertSql . $deleteSql . $updateSql;

        if (! $allSql) {
            exit('NoDiffSqls');
        }

        echo $allSql;
        exit;
    }

    // 执行生成insert
    private function _createInsert($conn1, $db, $table, $pks)
    {
        $insertSql = '';

        foreach ($pks as $pk) {

            // 查找当前的数据
            $data = $this->_fetchRow($conn1, $db, $table, $pk);

            $sql = $this->_buildInsertSql($data);

            $insertSql .=  "INSERT INTO `{$table}` (" . $sql['key_sql'] . ") VALUES (" . $sql['value_sql'] . ");" . PHP_EOL;
        }

        return $insertSql;
    }

    // 执行生成delete
    private function _createDelete($table, $pks)
    {
        $sql = '';

        foreach ($pks as $pk) {
            $sql .=  "DELETE FROM `{$table}` WHERE `id` = '{$pk}';" . PHP_EOL;
        }

        return $sql;
    }

    // 执行生成update
    private function _createUpdate($conn1, $conn2, $sourceDb, $destDb, $table, $excepts, $pks)
    {
        $updateSql = '';
        $sourceData = $this->_fetchAll($conn1, $sourceDb, $table, $pks);
        $destData = $this->_fetchAll($conn2, $destDb, $table, $pks);

        foreach ($sourceData as $i => $rowData) {

            $setArr = array();

            // 排除不需要更新的字段
            foreach ($excepts as $except) {
                if (isset($rowData[$except])) {
                    unset($rowData[$except]);
                }
            }

            // 比较每个字段
            foreach ($rowData as $field => $value) {

                if (! array_key_exists($field, $destData[$i])) {
                    exit($table . ' '. $field . ' construct is different');
                }

                if ($value != $destData[$i][$field]) {
                    $setArr[$field] = $value;
                }
            }

            if ($setArr) {
                $updateSql .= "UPDATE `{$table}` SET " . $this->_buildSetSql($setArr) . " WHERE `id` = '{$rowData['id']}' " . $this->_buildCondtions(array_keys($setArr), $destData[$i]) . ";" . PHP_EOL;
            }
        }

        return  $updateSql;
    }

    private function _buildCondtions(array $fields, array $destDataRow)
    {
        $condtions = '';

        foreach ($fields as $field) {
            $oldValue = addslashes($destDataRow[$field]);
            $condtions .= " AND `{$field}` = '{$oldValue}'";
        }

        return $condtions;
    }

    // 得到所有的表名
    private function _getTables($conn, $db)
    {
        $tables = array();

        mysql_select_db($db, $conn);

        $query = mysql_query('SHOW TABLES', $conn);

        while ($value = mysql_fetch_array($query, MYSQL_NUM)) {
            $tables[] = $value[0];
        }

        mysql_free_result($query);

        return $tables;
    }

    // 判断库是否存在
    private function _isDbExist($conn, $db)
    {
        $query = mysql_query("SHOW DATABASES LIKE '{$db}'", $conn);

        return mysql_fetch_array($query) ? true : false;
    }

    // 判断表是否存在
    private function _isTableExist($conn, $db, $table)
    {
        mysql_select_db($db, $conn);

        $query = mysql_query("SHOW TABLES LIKE '{$table}'", $conn);

        return mysql_fetch_array($query) ? true : false;
    }

    // 得到所有的主键
    private function _fetchPks($conn, $db, $table)
    {
        $pks = array();

        mysql_select_db($db, $conn);
        $query = mysql_query("SELECT `id` FROM `{$table}`", $conn);

        while ($value = mysql_fetch_array($query)) {
            $pks[] = $value['id'];
        }

        mysql_free_result($query);
        return $pks;
    }

    // 得到所有的记录
    private function _fetchAll($conn, $db, $table, $pks = null)
    {
        $list = array();

        $where = '';
        if ($pks) {
            $where = 'AND `id` IN (' . $this->_ximplode($pks). ')';
        }

        mysql_select_db($db, $conn);
        $query = mysql_query("SELECT * FROM `{$table}` WHERE 1 {$where} ORDER BY `id` ASC", $conn);

        while ($value = mysql_fetch_array($query, MYSQL_ASSOC)) {
            $list[] = $value;
        }

        mysql_free_result($query);
        return $list;
    }

    // 得到一条记录
    private function _fetchRow($conn, $db, $table, $pk)
    {
        mysql_select_db($db, $conn);
        $query = mysql_query("SELECT * FROM `{$table}` WHERE `id` = {$pk}", $conn);
        $result = mysql_fetch_array($query, MYSQL_ASSOC);

        mysql_free_result($query);
        return $result;
    }

    // 得到update的sql
    private function _buildSetSql(array $setArr)
    {
        $setSql = $comma = '';

        foreach ($setArr as $key => $value) {

            // 在预定义字符前添加反斜杠。
            $value = addslashes($value);
            $setSql .= $comma . "`{$key}` = '{$value}'";
            $comma = ', ';
        }

        return $setSql;
    }


    // 得到insert的sql
    private function _buildInsertSql(array &$setArr)
    {
        $insertkeysql = $insertvaluesql = $comma = '';
        foreach ($setArr as $key => $value) {
            if (false === $value) {
                unset($setArr[$key]);
                continue;
            }
            // 在预定义字符前添加反斜杠。
            $value = addslashes($value);

            $insertkeysql .= $comma . '`' . $key . '`';
            $insertvaluesql .= $comma . "'{$value}'" ;
            $comma = ', ';
        }

        $sql = array(
            'key_sql' => $insertkeysql,
            'value_sql' => $insertvaluesql
        );

        return $sql;
    }

    /**
     * 逗号连接
     *
     * @param array $array
     * @return string
     */
    private function _ximplode($array)
    {
        return empty($array) ? 0 : "'" . implode("','", is_array($array) ? $array : array($array)) . "'";
    }
}

$c = new Controller_CreateDiffSql();
$c->execute();